using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using LCL.LData;

namespace LCL.DbObjects.SQLServer {
    [Serializable]
    [DebuggerDisplay("{ConnectionString}")]
    public class DbObject:DbModelBase, IDbObject {
        public List<DataBaseModel> GetDBList(string host) {
            if(string.IsNullOrWhiteSpace(host)) return new List<DataBaseModel>();
            string cacheKey = string.Format(CacheKey_DBLIST,host);
            return cache.Get(cacheKey,() => {
                List<DataBaseModel> dbmList = new List<DataBaseModel>();
                var dt = DbHelper.ExecuteDataTable("use master;select name from sysdatabases order by name;",null);
                foreach(DataRow row in dt.Rows) {
                    string dbName = row["name"].ToString();
                    if(!this.ArrNoDblist.Contains(dbName)) {
                        DataBaseModel dbm = new DataBaseModel();
                        dbm.DatabaseName = dbName;
                        dbmList.Add(dbm);
                    }
                }
                return dbmList;
            });
        }
        public List<TableModel> GetTables(string dbName,bool iscol = false) {
            if(string.IsNullOrWhiteSpace(dbName)) return new List<TableModel>();
            string cacheKey = string.Format(CacheKey_TABLELIST,dbName);
            return cache.Get(cacheKey,() => {
                string sql = @"use {0} select * from (SELECT 
CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名, 
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明
FROM dbo.syscolumns a LEFT OUTER JOIN   
dbo.systypes b ON a.xtype = b.xusertype INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND  
d.status >= 0 LEFT OUTER JOIN   dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN sys.extended_properties g 
ON a.id = g.major_id AND a.colid = g.minor_id AND  g.name = 'MS_Description' LEFT OUTER JOIN  
sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 AND f.name = 'MS_Description')ltables 
where  isnull(表名,'')<>'' order by  表名 ";

                var list = new List<TableModel>();
                var dt = DbHelper.ExecuteDataTable(string.Format(sql,dbName),null);
                foreach(DataRow row in dt.Rows) {
                    string tableName = row["表名"].ToString();
                    if(!this.ArrNoTableslist.Contains(tableName)) {
                        var tm = new TableModel();
                        tm.TableName = tableName;
                        tm.TableNameRemark = row["表说明"].ToString();
                        if(iscol) {
                            if(tm.Columns == null || tm.Columns.Count == 0) {
                                tm.Columns = GetColumnInfoList(dbName,tm.TableName);
                                if(tm.Columns.Find(p => this.ArrTree.Contains(p.ColumnName)) != null) {
                                    tm.IsTree = true;
                                }
                                var mo = tm.Columns.FindLast(p => p.IsPrimaryKey == true);
                                if(mo != null) {
                                    tm.TablePK = mo.ColumnName;
                                }
                            }
                        }

                        list.Add(tm);
                    }
                }
                return list;
            });
        }
        public List<ColumnInfo> GetColumnInfoList(string dbName,string tableName) {
            if(string.IsNullOrWhiteSpace(dbName) || string.IsNullOrWhiteSpace(tableName)) return null;
            string cacheKey = string.Format(CacheKey_TABLE_COL,dbName,tableName);
            return cache.Get(cacheKey,() => {
                string sql = @"use {0} SELECT 
d.name  AS 表名, 
isnull(f.value, '') AS 表说明, 
a.colorder AS 字段序号, 
a.name AS 字段名,ISNULL(g.[value], '') AS 字段说明, 
CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识, 
CASE WHEN EXISTS(SELECT 1  FROM dbo.sysindexes si INNER JOIN 
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc 
ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN  dbo.sysobjects so 
ON so.name = si.name AND so.xtype = 'PK' WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键, 
b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')AS 精度, 
ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, 
ISNULL(e.text, '') AS 默认值 FROM dbo.syscolumns a LEFT OUTER JOIN   
dbo.systypes b ON a.xtype = b.xusertype INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND  
d.status >= 0 LEFT OUTER JOIN   dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN sys.extended_properties g 
ON a.id = g.major_id AND a.colid = g.minor_id AND  g.name = 'MS_Description' LEFT OUTER JOIN  
sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 AND f.name = 'MS_Description'  ";
                if(!string.IsNullOrWhiteSpace(tableName)) {
                    sql = sql + " where d.name='" + tableName + "'";
                }
                var list = new List<ColumnInfo>();
                var dt = DbHelper.ExecuteDataTable(string.Format(sql,dbName),null);
                foreach(DataRow row in dt.Rows) {
                    var tc = new ColumnInfo();
                    tc.TableName = row["表名"].ToString();
                    tc.ColumnName = row["字段名"].ToString();
                    tc.Description = row["字段说明"].ToString();
                    tc.TypeName = row["类型"].ToString();
                    tc.DefaultVal = row["默认值"].ToString();
                    tc.Length = row["长度"].ToString();
                    tc.ColumnOrder = row["字段序号"].ToString();
                    if(row["标识"].ToString().Length > 0) {
                        tc.IsIdentity = true;
                    }
                    if(row["主键"].ToString() == "√") {
                        tc.IsPrimaryKey = true;
                    }
                    if(row["允许空"].ToString() == "√") {
                        tc.Nullable = true;
                    }
                    list.Add(tc);
                }
                return list;
            });
        }
        public List<TableRelation> GetTableRelation(string tableName) {
            string cacheKey = string.Format(CacheKey_TABLE_COL,"key",tableName) + "_relation";
            return cache.Get(cacheKey,() => {
                #region 数据库读取
                string sql_tablesInfos = @"SELECT 
外键表名称 = object_name(b.fkeyid) ,
外键列名   = (SELECT name FROM syscolumns WHERE colid = b.fkey AND id = b.fkeyid) ,
主键表名   = object_name(b.rkeyid) ,
主键列名   = (SELECT name FROM syscolumns WHERE colid = b.rkey AND id = b.rkeyid) ,
级联更新   = ObjectProperty(a.id,'CnstIsUpdateCascade') ,
级联删除   = ObjectProperty(a.id,'CnstIsDeleteCascade') 
FROM sysobjects a 
JOIN sysforeignkeys b ON a.id = b.constid 
JOIN sysobjects c ON a.parent_obj = c.id 
WHERE a.xtype = 'F' AND c.xtype = 'U' and object_name(b.fkeyid)='" + tableName + "' ";
                var list = new List<TableRelation>();
                var dt = DbHelper.ExecuteDataTable(sql_tablesInfos,null);
                foreach(DataRow row in dt.Rows) {
                    var tr = new TableRelation();
                    tr.FTableName = row["外键表名称"].ToString();
                    tr.FColumnsName = row["外键列名"].ToString();
                    tr.RTableName = row["主键表名"].ToString();
                    tr.RColumnsName = row["主键列名"].ToString();
                    if(row["级联更新"].ToString() != "0") {
                        tr.CnstIsUpdateCascade = true;
                    }
                    if(row["级联删除"].ToString() != "0") {
                        tr.CnstIsDeleteCascade = true;
                    }
                    list.Add(tr);
                }
                return list;
                #endregion
            });
        }
        public void AddTableByKey(string dbName) {
            var list = this.GetTables(dbName);
            foreach(var item in list) {
                try {
                    DbHelper.ExecuteNonQuery("ALTER TABLE " + item.TableName + " ADD PRIMARY KEY(ID) ",null);
                    string cacheKey = CacheKey_TABLE_COL + item.TableName;
                    cache.Remove(cacheKey);
                } catch(Exception) {
                    continue;
                }
            }

        }
        public void ExtendedProperty(bool addbyupdate,string describe,string tableName,string columnName = null) {
            string sql_propertyInfo = @"EXEC sys.{0} N'MS_Description',N'{1}',N'SCHEMA',N'dbo',N'TABLE',N'{2}',N'COLUMN',N'{3}'";//字段说明
            string sql_propertyInfo1 = @"EXEC sys.{0} N'MS_Description',N'{1}',N'SCHEMA',N'dbo',N'TABLE',N'{2}'";//表说明
            string sql = "";
            if(addbyupdate) {
                if(columnName == null || columnName.Length == 0) {
                    sql = string.Format(sql_propertyInfo1,"sp_updateextendedproperty",describe,tableName);
                } else {
                    sql = string.Format(sql_propertyInfo,"sp_updateextendedproperty",describe,tableName,columnName);
                }
            } else {
                if(columnName == null || columnName.Length == 0) {
                    sql = string.Format(sql_propertyInfo1,"sp_addextendedproperty",describe,tableName);
                } else {
                    sql = string.Format(sql_propertyInfo,"sp_addextendedproperty",describe,tableName,columnName);
                }
            }
            DbHelper.ExecuteNonQuery(sql,null);
            string cacheKey = CacheKey_TABLE_COL + tableName;
            cache.Remove(cacheKey);
        }
        public TableModel GetTableInfo(string dbName,string tableName) {
            if(string.IsNullOrWhiteSpace(dbName) || string.IsNullOrWhiteSpace(tableName)) return null;
            string cacheKey = string.Format(CacheKey_TABLE_INFO,dbName,tableName);
            return cache.Get(cacheKey,() => {
                var tables = this.GetTables(dbName);
                TableModel tm = tables.FirstOrDefault(p => p.TableName == tableName);
                if(tm.Columns == null || tm.Columns.Count == 0) {
                    tm.Columns = GetColumnInfoList(dbName,tableName);
                }
                return tm;
            });
        }
    }
}
